from elasticsearch import Elasticsearch
from openpyxl import Workbook

# 配置 ES 连接（根据实际情况修改）
# es = Elasticsearch(
#     hosts=["http://localhost:9200"],
#     basic_auth=("用户名", "密码")  # 若无认证可省略
# )

# 假设查询结果中的 buckets 数据（替换为实际获取逻辑）
buckets = [
    # 此处粘贴你的查询结果中的 "buckets" 数组内容
    {
        "key": 1,
        "doc_count": 82211,
        "unique_crmUserId": {
            "value": 156
        }
    },
    {
        "key": 2,
        "doc_count": 93919,
        "unique_crmUserId": {
            "value": 359
        }
    },
    {
        "key": 11,
        "doc_count": 24538,
        "unique_crmUserId": {
            "value": 254
        }
    },
    {
        "key": 39,
        "doc_count": 67945,
        "unique_crmUserId": {
            "value": 608
        }
    },
    {
        "key": 40,
        "doc_count": 104935,
        "unique_crmUserId": {
            "value": 261
        }
    },
    {
        "key": 43,
        "doc_count": 19546,
        "unique_crmUserId": {
            "value": 198
        }
    },
    {
        "key": 44,
        "doc_count": 20999,
        "unique_crmUserId": {
            "value": 325
        }
    },
    {
        "key": 46,
        "doc_count": 52472,
        "unique_crmUserId": {
            "value": 470
        }
    },
    {
        "key": 47,
        "doc_count": 14842,
        "unique_crmUserId": {
            "value": 165
        }
    },
    {
        "key": 48,
        "doc_count": 10033,
        "unique_crmUserId": {
            "value": 197
        }
    },
    {
        "key": 49,
        "doc_count": 17335,
        "unique_crmUserId": {
            "value": 119
        }
    },
    {
        "key": 50,
        "doc_count": 52167,
        "unique_crmUserId": {
            "value": 182
        }
    },
    {
        "key": 51,
        "doc_count": 3180,
        "unique_crmUserId": {
            "value": 52
        }
    },
    {
        "key": 53,
        "doc_count": 8507,
        "unique_crmUserId": {
            "value": 41
        }
    },
    {
        "key": 59,
        "doc_count": 2444,
        "unique_crmUserId": {
            "value": 30
        }
    },
    {
        "key": 60,
        "doc_count": 53356,
        "unique_crmUserId": {
            "value": 136
        }
    },
    {
        "key": 61,
        "doc_count": 95231,
        "unique_crmUserId": {
            "value": 531
        }
    },
    {
        "key": 62,
        "doc_count": 72709,
        "unique_crmUserId": {
            "value": 355
        }
    },
    {
        "key": 63,
        "doc_count": 27189,
        "unique_crmUserId": {
            "value": 293
        }
    },
    {
        "key": 64,
        "doc_count": 1520,
        "unique_crmUserId": {
            "value": 49
        }
    },
    {
        "key": 65,
        "doc_count": 1173,
        "unique_crmUserId": {
            "value": 36
        }
    },
    {
        "key": 66,
        "doc_count": 10474,
        "unique_crmUserId": {
            "value": 101
        }
    },
    {
        "key": 67,
        "doc_count": 23679,
        "unique_crmUserId": {
            "value": 159
        }
    },
    {
        "key": 68,
        "doc_count": 699,
        "unique_crmUserId": {
            "value": 14
        }
    },
    {
        "key": 72,
        "doc_count": 10896,
        "unique_crmUserId": {
            "value": 68
        }
    },
    {
        "key": 73,
        "doc_count": 57454,
        "unique_crmUserId": {
            "value": 244
        }
    },
    {
        "key": 74,
        "doc_count": 178953,
        "unique_crmUserId": {
            "value": 464
        }
    },
    {
        "key": 75,
        "doc_count": 9038,
        "unique_crmUserId": {
            "value": 37
        }
    },
    {
        "key": 76,
        "doc_count": 6759,
        "unique_crmUserId": {
            "value": 65
        }
    },
    {
        "key": 78,
        "doc_count": 567,
        "unique_crmUserId": {
            "value": 27
        }
    },
    {
        "key": 79,
        "doc_count": 11998,
        "unique_crmUserId": {
            "value": 70
        }
    },
    {
        "key": 80,
        "doc_count": 39901,
        "unique_crmUserId": {
            "value": 155
        }
    },
    {
        "key": 81,
        "doc_count": 40886,
        "unique_crmUserId": {
            "value": 180
        }
    },
    {
        "key": 82,
        "doc_count": 1896,
        "unique_crmUserId": {
            "value": 26
        }
    },
    {
        "key": 83,
        "doc_count": 11951,
        "unique_crmUserId": {
            "value": 59
        }
    },
    {
        "key": 84,
        "doc_count": 34810,
        "unique_crmUserId": {
            "value": 59
        }
    },
    {
        "key": 86,
        "doc_count": 130,
        "unique_crmUserId": {
            "value": 1
        }
    },
    {
        "key": 87,
        "doc_count": 23940,
        "unique_crmUserId": {
            "value": 388
        }
    },
    {
        "key": 90,
        "doc_count": 3511,
        "unique_crmUserId": {
            "value": 38
        }
    },
    {
        "key": 91,
        "doc_count": 2600,
        "unique_crmUserId": {
            "value": 52
        }
    },
    {
        "key": 92,
        "doc_count": 9131,
        "unique_crmUserId": {
            "value": 66
        }
    },
    {
        "key": 93,
        "doc_count": 349,
        "unique_crmUserId": {
            "value": 11
        }
    },
    {
        "key": 94,
        "doc_count": 1362,
        "unique_crmUserId": {
            "value": 20
        }
    },
    {
        "key": 95,
        "doc_count": 5837,
        "unique_crmUserId": {
            "value": 102
        }
    },
    {
        "key": 97,
        "doc_count": 25679,
        "unique_crmUserId": {
            "value": 322
        }
    },
    {
        "key": 98,
        "doc_count": 2425,
        "unique_crmUserId": {
            "value": 34
        }
    },
    {
        "key": 100,
        "doc_count": 1893,
        "unique_crmUserId": {
            "value": 33
        }
    },
    {
        "key": 101,
        "doc_count": 208,
        "unique_crmUserId": {
            "value": 15
        }
    },
    {
        "key": 102,
        "doc_count": 1227,
        "unique_crmUserId": {
            "value": 24
        }
    },
    {
        "key": 103,
        "doc_count": 29543,
        "unique_crmUserId": {
            "value": 104
        }
    },
    {
        "key": 104,
        "doc_count": 2235,
        "unique_crmUserId": {
            "value": 42
        }
    },
    {
        "key": 105,
        "doc_count": 5,
        "unique_crmUserId": {
            "value": 4
        }
    },
    {
        "key": 106,
        "doc_count": 2758,
        "unique_crmUserId": {
            "value": 49
        }
    },
    {
        "key": 107,
        "doc_count": 28398,
        "unique_crmUserId": {
            "value": 59
        }
    },
    {
        "key": 113,
        "doc_count": 1174,
        "unique_crmUserId": {
            "value": 18
        }
    },
    {
        "key": 114,
        "doc_count": 116,
        "unique_crmUserId": {
            "value": 9
        }
    },
    {
        "key": 128,
        "doc_count": 15,
        "unique_crmUserId": {
            "value": 5
        }
    },
    {
        "key": 132,
        "doc_count": 696,
        "unique_crmUserId": {
            "value": 12
        }
    },
    {
        "key": 145,
        "doc_count": 552,
        "unique_crmUserId": {
            "value": 13
        }
    },
    {
        "key": 146,
        "doc_count": 36236,
        "unique_crmUserId": {
            "value": 57
        }
    },
    {
        "key": 147,
        "doc_count": 359,
        "unique_crmUserId": {
            "value": 24
        }
    },
    {
        "key": 148,
        "doc_count": 526,
        "unique_crmUserId": {
            "value": 27
        }
    },
    {
        "key": 151,
        "doc_count": 455,
        "unique_crmUserId": {
            "value": 8
        }
    },
    {
        "key": 202,
        "doc_count": 1830,
        "unique_crmUserId": {
            "value": 26
        }
    },
    {
        "key": 203,
        "doc_count": 20,
        "unique_crmUserId": {
            "value": 6
        }
    },
    {
        "key": 237,
        "doc_count": 621,
        "unique_crmUserId": {
            "value": 9
        }
    },
    {
        "key": 239,
        "doc_count": 8764,
        "unique_crmUserId": {
            "value": 70
        }
    },
    {
        "key": 240,
        "doc_count": 43164,
        "unique_crmUserId": {
            "value": 186
        }
    },
    {
        "key": 360210,
        "doc_count": 1,
        "unique_crmUserId": {
            "value": 1
        }
    },
    {
        "key": 420210,
        "doc_count": 4989,
        "unique_crmUserId": {
            "value": 49
        }
    },
    {
        "key": 420211,
        "doc_count": 40,
        "unique_crmUserId": {
            "value": 3
        }
    },
    {
        "key": 450210,
        "doc_count": 16145,
        "unique_crmUserId": {
            "value": 39
        }
    }
    # ... 其他 bucket 数据
]

# 创建 Excel 工作簿
wb = Workbook()
ws = wb.active
ws.title = "ES 统计结果"

# 添加表头
ws.append(["学校 ID (schoolId)", "文档总数 (doc_count)", "不重复 crmUserId 总数"])

# 填充数据
for bucket in buckets:
    school_id = bucket["key"]
    doc_count = bucket["doc_count"]
    unique_users = bucket["unique_crmUserId"]["value"]
    ws.append([school_id, doc_count, unique_users])

# 保存 Excel 文件
excel_filename = "/Users/mabin/Downloads/es_statistics.xlsx"
wb.save(excel_filename)
print(f"数据已导出到 {excel_filename}")